/*
 *此javaCode由YMS-JDBC替换工具生成，原mapper位置com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.mapper.CheckresultMapper
 */
package com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.mapper;

import com.yonyou.iuap.yms.api.IYmsJdbcApi;
import com.yonyou.iuap.yms.param.ResultSetProcessor;
import com.yonyou.iuap.yms.param.SQLParameter;
import com.yonyou.iuap.yms.processor.AutoBeanListProcessor;
import com.yonyou.iuap.yms.processor.AutoBeanProcessor;
import com.yonyou.iuap.yms.processor.ColumnProcessor;
import com.yonyou.iuap.yms.replace.CollectionUtil;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.entity.Checkresult;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.entity.Checkresult2;
import com.yonyou.ucf.mdf.app.isvdxq.secondpart.model.CheckResultBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Types;
import java.util.List;

@Repository
public class CheckresultMapperImpl {
    
    @Autowired
    private IYmsJdbcApi ymsJdbcApi;

    public List<Checkresult> selectBycheckAreaID(String checkLocationID, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select * from dxq_checkresult where checkLocationID=? and ytenant_id=? and `dr`=0;\n";
        parameter.addParam(checkLocationID);
		parameter.addParam(tenant_id);

        ResultSetProcessor processor = new AutoBeanListProcessor(new Checkresult());
        return (List<Checkresult>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public int InsertResult(String checkNo, String id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "INSERT INTO `dxq_checkresult`(`checkNo`,id) values (?,?);\n";
        parameter.addParam(checkNo);
		parameter.addParam(id);

        
        return ymsJdbcApi.update(sql, parameter);
    }


    public List<Checkresult> selectBycheckID(String checkID, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select * from dxq_checkresult where checkID=? and ytenant_id=? and `dr`=0;\n";
        parameter.addParam(checkID);
		parameter.addParam(tenant_id);

        ResultSetProcessor processor = new AutoBeanListProcessor(new Checkresult());
        return (List<Checkresult>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }

	public List<Checkresult2> selectYichangBycheckId(String checkID, String tenant_id) {
		SQLParameter parameter = new SQLParameter(true);

		String sql = "select a.id, a.checkID, a.coderfID, a.storkLocationID, a.product_id, a.product_sku_id, " +
				"a.remark, a.checkstatus, a.LabelCount, b.product_name, b.rfid_code, b.warehouse_id, b.batch_no " +
				"from dxq_checkresult a left join dxq_coderfid b on a.coderfID = b.id " +
				"where a.checkID=? and a.ytenant_id=? and (a.checkstatus=0 or a.checkstatus=2) and a.`dr`=0;\n";
		parameter.addParam(checkID);
		parameter.addParam(tenant_id);

		ResultSetProcessor processor = new AutoBeanListProcessor(new Checkresult2());
		return (List<Checkresult2>) ymsJdbcApi.queryForList(sql, parameter, processor);
	}

    public Checkresult selectById(String id, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select * from dxq_checkresult where id=? and ytenant_id=? and `dr`=0;\n";
        parameter.addParam(id);
		parameter.addParam(tenant_id);

        ResultSetProcessor processor = new AutoBeanProcessor(new Checkresult());
        return (Checkresult) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public List<Checkresult> getByCheckAreaIds(String checkId, List<String> locationIds, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select * from dxq_checkresult where checkID=? ");
		parameter.addParam(checkId);
		if (CollectionUtil.isNotEmpty(locationIds)) {
			sqlBuilder.append(" and checkLocationID in ( ");
			String separator0 = " , ";
			for (int index = 0; index < locationIds.size(); index++) {
				String item = locationIds.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(item);
				if (index < locationIds.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		sqlBuilder.append(" and ytenant_id=? and `dr`=0; ");
		parameter.addParam(tenant_id);

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new Checkresult());
        return (List<Checkresult>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public int selectCheckCount(String checkid, List<String> checkLocationArr, String productName, String productunit, String stockunitname, String checkstatus, String iHandelMethod, String scanWay, String productskuName, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" SELECT COUNT(c.`id`) AS numcount FROM dxq_checkresult c LEFT JOIN `dxq_coderfid` d ON c.`coderfID`=d.`id` ");
		sqlBuilder.append(" left join dxqyy_product p on d.product_id= p.id left join dxqyy_productskutemp p1 on p1.id=d.product_sku_id ");
		sqlBuilder.append(" LEFT JOIN `dxq_checkstock` k ON c.`checkID`=k.`id` left join dxqyy_unit u on u.id=p.unit where c.checkID=? ");
		parameter.addParam(checkid);
        if (CollectionUtil.isNotEmpty(checkLocationArr)) {
            sqlBuilder.append(" and c.checkLocationID in ");
            sqlBuilder.append(" ( ");
            String separator0 = " , ";
            for (int index = 0; index < checkLocationArr.size(); index++) {
                String item = checkLocationArr.get(index);
                sqlBuilder.append(" ? ");
                parameter.addParam(item);
                if (index < checkLocationArr.size() - 1) {
                    sqlBuilder.append(separator0);
                }
            }
            sqlBuilder.append(" ) ");
        }
		if (productName != null && !"".equals(productName)) {
			sqlBuilder.append(" and p.`productName` like CONCAT('%',?,'%') ");
			parameter.addParam(productName);
		}
		if (productunit != null && !"".equals(productunit)) {
			sqlBuilder.append(" and u.`unitName` like CONCAT('%',?,'%') ");
			parameter.addParam(productunit);
		}
		if (stockunitname != null && !"".equals(stockunitname)) {
			sqlBuilder.append(" and d.`stock_unit_name` like CONCAT('%',?,'%') ");
			parameter.addParam(stockunitname);
		}
		if (checkstatus != null && !"".equals(checkstatus)) {
			sqlBuilder.append(" and c.checkstatus=? ");
			parameter.addParam(checkstatus);
		}
		if (iHandelMethod != null && !"".equals(iHandelMethod)) {
			sqlBuilder.append(" and c.iHandelMethod=? ");
			parameter.addParam(iHandelMethod);
		}
		if (scanWay != null && !"".equals(scanWay)) {
			sqlBuilder.append(" and c.scanWay=? ");
			parameter.addParam(scanWay);
		}
		if (productskuName != null && !"".equals(productskuName)) {
			sqlBuilder.append(" and p1.`skuName` like CONCAT('%',?,'%') ");
			parameter.addParam(productskuName);
		}
		sqlBuilder.append(" AND c.dr=0 and c.ytenant_id=? ORDER BY c.`create_time` DESC ");
		parameter.addParam(tenant_id);

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new ColumnProcessor(int.class);
        return (int) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public List<Checkresult> selcheckresultById(String checkID, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select * from dxq_checkresult where checkID=? and dr=0 and (checkstatus=0||checkstatus=2) and ytenant_id=?  order by create_time desc\n";
        parameter.addParam(checkID);
		parameter.addParam(tenant_id);

        ResultSetProcessor processor = new AutoBeanListProcessor(new Checkresult());
        return (List<Checkresult>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public List<Checkresult> selectBycheckParam(String checkid, List<String> checkLocationArr, String productName, String productunit, String stockunitname, String checkstatus, String iHandelMethod, String scanWay, String productskuName, int startNumber, int pagesize, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" SELECT c.`checkID`,k.`cCheckCode` AS `checkCode`,c.`checkAreaID`,c.`coderfID`,d.`f_stock_count` AS `LabelCount`,d.`product_id`, p.`productName`,p1.`skuName` as productskuName, ");
		sqlBuilder.append(" u.`unitName` AS `productunit`,d.`stock_unit_name` AS `stockunitName`,d.`product_sku_id`,c.`checkLocationID`,c.`storkLocationID`,c.`remark`,c.`checkUserName`,c.`checkUser`,c.`checkRemark`,c.`checkstatus`, ");
		sqlBuilder.append(" c.`scanWay`,c.`checkNum`,c.`iHandelMethod`,c.`fHandleCount`,c.`HandeluserName`,c.`HandeluserID`,c.`HandelRemark`,c.`HandelTime`,c.`dr`, c.`tenant_id`, c.`ytenant_id`, c.`pk_temp`,c.`version`,c.`create_time`,c.`modify_time`, ");
		sqlBuilder.append(" c.`creator`,c.`modifier`,c.`id`,c.`pubts` ");
		sqlBuilder.append(" FROM dxq_checkresult c LEFT JOIN `dxq_coderfid` d ON c.`coderfID`=d.`id` left join dxqyy_product p on d.product_id= p.id left join dxqyy_productskutemp p1 on p1.id=d.product_sku_id ");
		sqlBuilder.append(" LEFT JOIN `dxq_checkstock` k ON c.`checkID`=k.`id` left join dxqyy_unit u on u.id=p.unit where checkID=? ");
		parameter.addParam(checkid);
        if (CollectionUtil.isNotEmpty(checkLocationArr)) {
            sqlBuilder.append(" and c.checkLocationID in ");
            sqlBuilder.append(" ( ");
            String separator0 = " , ";
            for (int index = 0; index < checkLocationArr.size(); index++) {
                String item = checkLocationArr.get(index);
                sqlBuilder.append(" ? ");
                parameter.addParam(item);
                if (index < checkLocationArr.size() - 1) {
                    sqlBuilder.append(separator0);
                }
            }
            sqlBuilder.append(" ) ");
		}
		if (productName != null && !"".equals(productName)) {
			sqlBuilder.append(" and p.`productName` like CONCAT('%',?,'%') ");
			parameter.addParam(productName);
		}
		if (productunit != null && !"".equals(productunit)) {
			sqlBuilder.append(" and u.`unitName` like CONCAT('%',?,'%') ");
			parameter.addParam(productunit);
		}
		if (stockunitname != null && !"".equals(stockunitname)) {
			sqlBuilder.append(" and d.`stock_unit_name` like CONCAT('%',?,'%') ");
			parameter.addParam(stockunitname);
		}
		if (checkstatus != null && !"".equals(checkstatus)) {
			sqlBuilder.append(" and c.checkstatus=? ");
			parameter.addParam(checkstatus);
		}
		if (iHandelMethod != null && !"".equals(iHandelMethod)) {
			sqlBuilder.append(" and c.iHandelMethod=? ");
			parameter.addParam(iHandelMethod);
		}
		if (scanWay != null && !"".equals(scanWay)) {
			sqlBuilder.append(" and c.scanWay=? ");
			parameter.addParam(scanWay);
		}
		if (productskuName != null && !"".equals(productskuName)) {
			sqlBuilder.append(" and p1.`skuName` like CONCAT('%',?,'%') ");
			parameter.addParam(productskuName);
		}
		sqlBuilder.append(" and c.dr=0 and c.ytenant_id=? ORDER BY c.`create_time` DESC LIMIT ?, ? ");
		parameter.addParam(tenant_id);
		parameter.addParam(startNumber);
		parameter.addParam(pagesize);

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new Checkresult());
        return (List<Checkresult>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public int deleteById(String checkID, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "delete from dxq_checkresult where checkID=? and ytenant_id=? and `dr`=0;\n";
        parameter.addParam(checkID);
		parameter.addParam(tenant_id);

        ResultSetProcessor processor = new ColumnProcessor(int.class);
        return (int) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public int InsertObj(Checkresult checkresult) throws Exception {
        SQLParameter parameter = new SQLParameter(true);

        String sql = "INSERT INTO `dxq_checkresult`(`checkNo`,id) values (?,?);\n";
        parameter.addParam(checkresult.getCheckCode());
		parameter.addParam(checkresult.getId());


        return ymsJdbcApi.update(sql, parameter);
    }


    public int insertBatch(List<CheckResultBean> insertBatch) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" insert into dxq_checkresult (`id`,`version`,`pk_temp`, `dr`,`tenant_id`,`ytenant_id`, `checkID`,`checkAreaID`,`checkCode`,`coderfID`,`LabelCount`, `product_id`,`product_sku_id`,`checkLocationID`,`storkLocationID`, `remark`,`checkUser`,`checkRemark`,`checkNum`, `iHandelMethod`,`checkstatus`,`scanWay`,`tagStatus`, `modify_time`,`modifier`,`create_time`,`creator`) values ");
		if (CollectionUtil.isNotEmpty(insertBatch)) {
			String separator0 = " , ";
			for (int i = 0; i < insertBatch.size(); i++) {
				CheckResultBean item = insertBatch.get(i);
				sqlBuilder.append(" (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
				parameter.addParamWithType(item.getId(), Types.VARCHAR);
				parameter.addParamWithType(item.getVersion(), Types.INTEGER);
				parameter.addParamWithType(item.getPk_temp(), Types.VARCHAR);
				parameter.addParamWithType(item.getDr(), Types.SMALLINT);
				parameter.addParamWithType(item.getTenant_id(), Types.VARCHAR);
				parameter.addParamWithType(item.getTenant_id(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckID(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckAreaID(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckCode(), Types.VARCHAR);
				parameter.addParamWithType(item.getCoderfID(), Types.VARCHAR);
				parameter.addParamWithType(item.getLabelCount(), Types.VARCHAR);
				parameter.addParamWithType(item.getProduct_id(), Types.VARCHAR);
				parameter.addParamWithType(item.getProduct_sku_id(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckLocationID(), Types.VARCHAR);
				parameter.addParamWithType(item.getStorkLocationID(), Types.VARCHAR);
				parameter.addParamWithType(item.getRemark(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckUser(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckRemark(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckNum(), Types.INTEGER);
				parameter.addParamWithType(item.getiHandelMethod(), Types.VARCHAR);
				parameter.addParamWithType(item.getCheckstatus(), Types.VARCHAR);
				parameter.addParamWithType(item.getScanWay(), Types.VARCHAR);
				parameter.addParamWithType(item.getTagStatus(), Types.VARCHAR);
				parameter.addParamWithType(item.getModify_time(), Types.TIMESTAMP);
				parameter.addParamWithType(item.getModifier(), Types.VARCHAR);
				parameter.addParamWithType(item.getCreate_time(), Types.TIMESTAMP);
				parameter.addParamWithType(item.getCreator(), Types.VARCHAR);
				if (i < insertBatch.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
		}
		sqlBuilder.append(" ON DUPLICATE KEY UPDATE coderfID=VALUES(coderfID) ,LabelCount=VALUES(labelCount),checkUser=VALUES(checkUser)," +
				"checkNum=VALUES(checkNum) ,product_id=VALUES(product_id),product_sku_id=VALUES(product_sku_id) ,checkLocationID=VALUES(checkLocationID)," +
				"storkLocationID=VALUES(storkLocationID) ,remark=VALUES(remark),checkRemark=VALUES(checkRemark) ,iHandelMethod=VALUES(iHandelMethod)," +
				"checkstatus=VALUES(checkstatus) ,scanWay=VALUES(scanWay),tagStatus=VALUES(tagStatus) ,modify_time=VALUES(modify_time),modifier=VALUES(modifier) ");

        
        String sql = sqlBuilder.toString();
        
        return ymsJdbcApi.update(sql, parameter);
    }


    public int insertCheckResultList(String CheckID, String CheckAreaID, String coderfID, String labelCount, String product_id, String product_sku_id, String checkLocationID, String storkLocationID, String status, String checkNum, String scanWay, String id, String tagStatus, String remark, String checkUser, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "INSERT INTO `dxq_checkresult`( `checkID`,`checkAreaID`,`coderfID`,`LabelCount`,`product_id`,`product_sku_id`,`checkLocationID`,`storkLocationID`,`remark`,`checkUser`,`checkRemark`,`checkstatus`,iHandelMethod, `checkNum`,`scanWay`,`dr`,`tenant_id`,`ytenant_id`,`pk_temp`,`version`,`create_time`,`modify_time`,`creator`,`modifier`,`id`,tagStatus)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,'0',?,?,0,?,?,'',0,now(),now(),?,?,?,?)\n";
        parameter.addParam(CheckID);
		parameter.addParam(CheckAreaID);
		parameter.addParam(coderfID);
		parameter.addParam(labelCount);
		parameter.addParam(product_id);
		parameter.addParam(product_sku_id);
		parameter.addParam(checkLocationID);
		parameter.addParam(storkLocationID);
		parameter.addParam(remark);
		parameter.addParam(checkUser);
		parameter.addParam(remark);
		parameter.addParam(status);
		parameter.addParam(checkNum);
		parameter.addParam(scanWay);
		parameter.addParam(tenant_id);
		parameter.addParam(tenant_id);
		parameter.addParam(checkUser);
		parameter.addParam(checkUser);
		parameter.addParam(id);
		parameter.addParam(tagStatus);

        
        return ymsJdbcApi.update(sql, parameter);
    }


    public int DelcheckresultByids(List<String> ids, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" update dxq_checkresult set dr=1 where ytenant_id=? and `dr`=0 ");
		parameter.addParam(tenant_id);
		if (CollectionUtil.isNotEmpty(ids)) {
			sqlBuilder.append(" and id in ( ");
			String separator0 = " , ";
			for (int index = 0; index < ids.size(); index++) {
				String item = ids.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(item);
				if (index < ids.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}else{
			sqlBuilder.append(" and id in ('')");
		}

        
        String sql = sqlBuilder.toString();
        
        return ymsJdbcApi.update(sql, parameter);
    }


    public int DelByLocationID(String checkAreaID, String checkLocationID, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "update dxq_checkresult set dr=1 where checkAreaID=? and checkLocationID=? and ytenant_id=? and `dr`=0;\n";
        parameter.addParam(checkAreaID);
		parameter.addParam(checkLocationID);
		parameter.addParam(tenant_id);

        
        return ymsJdbcApi.update(sql, parameter);
    }

}
